package m.common.model.util;

import m.common.model.*;
import m.common.model.config.ModelConfig;
import m.system.db.DBManager;
import m.system.db.DataRow;
import m.system.db.DataSet;
import m.system.exception.MException;
import m.system.lang.PageInfo;
import m.system.util.ClassUtil;
import m.system.util.StringUtil;
import m.system.util.SystemUtil;

import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class ModelQueryList implements IModelQuery,QueryList {
	private final Class<? extends Model> table;
	private final List<String> fieldNames;
	private final List<QueryCondition> conditions;
	private final List<QueryOrder> orders;
	private QueryPage page;
	private int int_a=0;
	private final Map<String,String> tableAMap;
	private QueryParameter parameter;
	private QueryParameter countParameter;
	private final Map<String,String> linkTableSqlMap;
	private List<String> fieldNameSqlList;
	private final Map<String,String> fieldTypeMap;
	private final Map<String,String> fieldToAliasMap;
	private final Map<String,String> noFieldAliasMap;
	private final Map<String,String> expressionMap;
	private final Map<String,String> expressionField;
	private final List<IModelQuery> unionList;
	private ModelQueryList(Class<? extends Model> clazz,String[] fieldNames,QueryPage page,QueryCondition condition,Map<String,String> expressionMap,QueryOrder... orders){
		this.table=clazz;
		this.fieldNames= new ArrayList<>();
		this.page=page;
		this.conditions=new ArrayList<>();
		if(null!=condition) this.conditions.add(condition);
		this.orders=new ArrayList<>();
		if(null!=orders&&orders.length>0) this.orders.addAll(Arrays.asList(orders));
		this.expressionMap=expressionMap;
		this.expressionField= new HashMap<>();
		this.tableAMap= new HashMap<>();
		this.linkTableSqlMap= new LinkedHashMap<>();
		this.fieldToAliasMap= new HashMap<>();
		this.noFieldAliasMap=new HashMap<>();
		this.fieldTypeMap=new HashMap<>();
		this.unionList=new ArrayList<>();
		addField(fieldNames);
	}
	/**
	 * 添加fieldName
	 *  fieldName
	 *
	 */
	public ModelQueryList addField(String... fieldName) {
		for(String f : fieldName) {
			if(!StringUtil.isSpace(f)) {
				List<String> ls=coverToFieldList(this.table, "", f);
				for(String fn : ls) {
					if(!this.fieldNames.contains(fn)) {
						this.fieldNames.add(fn);
					}
				}
			}
		}
		return this;
	}

	@Override
	public ModelQueryList addFieldExpression(String field, String expression) {
		this.expressionMap.put(field,expression);
		return this;
	}

	@Override
	public ModelQueryList addCondition(QueryCondition... cond) {
		this.conditions.addAll(Arrays.asList(cond));
		return this;
	}

	@Override
	public ModelQueryList setPage(QueryPage page) {
		this.page=page;
		return this;
	}

	@Override
	public ModelQueryList addOrder(QueryOrder... order) {
		this.orders.addAll(Arrays.asList(order));
		return this;
	}
	@Override
	public ModelQueryList unionAll(IModelQuery... querys) {
		this.unionList.addAll(Arrays.asList(querys));
		return this;
	}
	/**
	 * 获取sql查询的列别名, 通过fieldname
	 *  field
	 *
	 */
	public String getAlias4Field(String field){
		String str=this.fieldToAliasMap.get(field);
		if(null==str) {
			for(int i=0;i<this.fieldNames.size();i++) {
				if(this.fieldNames.get(i).equals(field)) {
					String fs=this.fieldNameSqlList.get(i);
					str=fs.substring(fs.lastIndexOf(" ")+1);
					break;
				}
			}
		}
		return str;
	}
	//转换属性，*转换成所有属性
	private <T extends Model> List<String> coverToFieldList(Class<T> clazz,String prefix,String attr){
		List<String> fieldList= new ArrayList<>();
		String ps=StringUtil.isSpace(prefix)?"": prefix + ".";
		if("*".equals(attr)){
			fieldList.add(ps + "oid");
			Map<String,FieldMeta> fs=ModelConfig.getFieldMetaMap(clazz);
			for(String key : fs.keySet()){
				fieldList.add(ps + key);
			}
			Map<String,LinkTableMeta> ls=ModelConfig.getLinkTableMetaMap(clazz);
			for(String key : ls.keySet()){
				fieldList.add(ps + key + ".oid");
			}
			Map<String, QueryFieldMeta> qs=ModelConfig.getQueryFieldMetaMap(clazz);
			for(String key : qs.keySet()){
				fieldList.add(ps + key);
				this.expressionMap.put(ps + key,convertExp(ps,qs.get(key).expression()));
			}
		}else{
			int i=attr.indexOf(".");
			if(i==-1){
				fieldList.add(ps + attr);
//				if(RuntimeData.isSecretField(clazz.getSimpleName() + "." + attr)) {//秘密字段输出null
//					this.expressionMap.put(ps + attr,"null");
//				}
				QueryFieldMeta qfm=ModelConfig.getQueryFieldMetaMap(clazz).get(attr);
				if(null!=qfm) this.expressionMap.put(ps + attr,convertExp(ps,qfm.expression()));
			}else{
				String fn=attr.substring(0, i);
				String newField=attr.substring(i+1);
				LinkTableMeta linkTable=ModelConfig.getLinkTableMetaMap(clazz).get(fn);
				if(null!=linkTable){
					fieldList.addAll(coverToFieldList(linkTable.table(), ps + fn, newField));
				}
			}
		}
		return fieldList;
	}
	private String convertExp(String a,String exp){
		Pattern pattern=Pattern.compile("\\#\\{.+?\\}");
		Matcher matcher=pattern.matcher(exp);
		while(matcher.find()){
			String str=matcher.group();
			String ns=str.substring(2,str.length()-1);
			exp = exp.replace(str, "#{"+a+ns+"}");
		}
		return exp;
	}
	/**
	 * 获取fieldNames对应的sqlList
	 *
	 *  MException
	 */
	private List<String> getFieldNameSqlList() throws MException{
		if(null==this.fieldNameSqlList){
			List<String> fieldNameSqlList= new ArrayList<>();
			if(null!=this.fieldNames){
				for(String fieldName : this.fieldNames){
					String fn=getFieldNameSql("t0", fieldName, table, true,fieldName);
					String exp=this.expressionMap.get(fieldName);
					if(!StringUtil.isSpace(exp)) {
						this.expressionMap.remove(fieldName);
						Pattern pattern=Pattern.compile("\\#\\{.+?\\}");
						Matcher matcher=pattern.matcher(exp);
						while(matcher.find()){
							String str=matcher.group();
							String ns=str.substring(2,str.length()-1);
							String fn1=getFieldNameSql("t0", ns, table, true,ns);
							fieldToAliasMap.put(ns, fn1.substring(fn1.lastIndexOf(" ")+1));
							exp=exp.replace(str, fn1.substring(0,fn1.lastIndexOf(" ")));
						}
						if(!StringUtil.isSpace(fn)) {
							this.expressionField.put(fn.substring(0, fn.lastIndexOf(" ")), exp);
							fieldNameSqlList.add(exp + fn.substring(fn.lastIndexOf(" ")));
						}else{
							this.expressionField.put(fieldName, exp);
							fieldNameSqlList.add(exp + " "+this.noFieldAliasMap.get(fieldName));
						}
					}else{
						if(!StringUtil.isSpace(fn)){
							fieldToAliasMap.put(fieldName, fn.substring(fn.lastIndexOf(" ")+1));
							fieldNameSqlList.add(fn);
						}
					}
				}
			}
			for(String key : this.expressionMap.keySet()) {
				String exp=this.expressionMap.get(key);
				if(StringUtil.isSpace(exp)) continue;
				Pattern pattern=Pattern.compile("\\#\\{.+?\\}");
				Matcher matcher=pattern.matcher(exp);
				while(matcher.find()){
					String str=matcher.group();
					String ns=str.substring(2,str.length()-1);
					String fn1=getFieldNameSql("t0", ns, table, true,ns);
					fieldToAliasMap.put(ns, fn1.substring(fn1.lastIndexOf(" ")+1));
					exp=exp.replace(str, fn1.substring(0,fn1.lastIndexOf(" ")));
				}
				this.expressionField.put(key,exp);
				fieldNameSqlList.add(exp+" "+key);
			}
			this.fieldNameSqlList=fieldNameSqlList;
		}
		return this.fieldNameSqlList;
	}
	protected String toExpressionField(String nameSql,String a) {
		String fn=expressionField.get(nameSql);
		if(null==fn) {
			return nameSql+a;
		}else {
			return fn+a;
		}
	}

	/**
	 * 获取fieldNameSql对应的数据类型
	 */
	protected String getFieldType(String fieldNameSql){
		String type=this.fieldTypeMap.get(fieldNameSql);
		return null==type?"STRING":type;
	}
	/**
	 * 递归方法,  返回对应的查询的fieldName, 并添加需要left join的 table
	 */
	protected String getFieldNameSql(String a,String fieldName,Class<? extends Model> clazz,boolean hasColName,String oldFieldName) throws MException{
		if("oid".equals(fieldName)){
			TableMeta tm=ModelConfig.getTableMeta(clazz);
			if(hasColName){
				return a + "."+tm.oidField()+" " + a + "_oid";
			}else{
				return a + "."+tm.oidField();
			}
		}
		int i=fieldName.indexOf(".");
		if(i==-1){
			FieldMeta fieldMeta=ModelConfig.getFieldMetaMap(clazz).get(fieldName);
			if(null!=fieldMeta){
				String fieldNameSql;
				if(hasColName){
					fieldNameSql=a + "." + fieldMeta.name() + " " + a + "_" + fieldName;
				}else{
					fieldNameSql=a + "." + fieldMeta.name();
				}
				this.fieldTypeMap.put(fieldNameSql,fieldMeta.type().toString());
				return fieldNameSql;
			}else{
				this.noFieldAliasMap.put(oldFieldName,a + "_" + fieldName);
			}
		}else{
			String fn=fieldName.substring(0, i);
			String newField=fieldName.substring(i+1);
			LinkTableMeta linkTable=ModelConfig.getLinkTableMetaMap(clazz).get(fn);
			if(null!=linkTable){
				TableMeta jtm=ModelConfig.getTableMeta(linkTable.table());
				String afn= a + "." + fn;
				if(newField.equals("oid")){
					if(hasColName){
						return a + "." + linkTable.name() + " " + a + "_" + fn;
					}else{
						return a + "." + linkTable.name();
					}
				}else{
					String linka=this.tableAMap.get(afn);
					if(null==linka){
						linka= "t" + (int_a + 1);
						String linkTableSql = " LEFT JOIN " + jtm.name() + " " + linka +
								" ON t" + (int_a + 1) + "."+jtm.oidField()+"=" + a + "." + linkTable.name();
						this.linkTableSqlMap.put(afn, linkTableSql);
						this.tableAMap.put(afn, linka);
						int_a++;
					}
					return getFieldNameSql(linka, newField, linkTable.table(), hasColName,fieldName);
				}
			}else {
				throw new MException("没有找到属性 " + fieldName);
			}
		}
		return null;
	}
	/**
	 * 返回 from段sql,  先执行getFieldNameSqlList()再调用才有效
	 *
	 */
	private String getFromSql(){
		TableMeta tableMeta=ModelConfig.getTableMeta(table);
		StringBuilder from=new StringBuilder();
		from.append(" FROM ").append(tableMeta.name()).append(" t0 ");
		if(null!=this.linkTableSqlMap){
			for(String linkTableSql : this.linkTableSqlMap.values()){
				from.append(linkTableSql);
			}
		}
		return from.toString();
	}
	/**
	 * 返回查询sql 和参数
	 *
	 *  MException
	 */
	public QueryParameter getQueryParameter() throws MException{
		if(null==this.parameter){
			List<String> fnSqlList=getFieldNameSqlList();
			if(!this.conditions.isEmpty()){
				this.parameter=QueryCondition.and(this.conditions.toArray(new QueryCondition[0])).toQueryParameter("t0", table, this);
			}else{
				this.parameter=new QueryParameter("", new ArrayList<>());
			}
			StringBuilder orderSql=new StringBuilder();
			if(null!=this.orders){
				for(QueryOrder order : this.orders){
					if(null!=order&&!StringUtil.isSpace(order.getName())) {
						String os=order.toSqlString("t0", table, this);
						String fn=toExpressionField(os.substring(0,os.lastIndexOf(" ")),os.substring(os.lastIndexOf(" ")));
						orderSql.append(fn).append(",");
					}
				}
				TableMeta tm=ModelConfig.getTableMeta(this.table);
				orderSql.append(QueryOrder.desc(tm.oidField()).toSqlString("t0", table, this));
			}
			if(fnSqlList.isEmpty()) throw new MException("未设置查询字段或字段无效");
			String from=getFromSql();
			StringBuilder sql=new StringBuilder();
			for(String fnSql : fnSqlList){
				sql.append(",").append(fnSql);
			}
			sql=new StringBuilder(sql.substring(1)).insert(0, "SELECT ").append(from);
			if(!StringUtil.isSpace(this.parameter.getSql())){
				sql.append(" WHERE ").append(this.parameter.getSql());
			}
			if(!unionList.isEmpty()) {
				for (IModelQuery query : unionList) {
					QueryParameter qp = query.getQueryParameter();
					sql.append(" UNION ALL ").append(qp.getSubSql());
					this.parameter.getValueList().addAll(qp.getValueList());
				}
			}else {
				if (orderSql.length() > 0) {
					sql.append(" ORDER BY ").append(orderSql);
				}
				if (null != this.page) {
					sql.append(this.page.toPageString());
				}
			}
			this.parameter.setSql(sql.toString());
		}
		return this.parameter;
	}
	public QueryParameter getCountParameter() throws MException{
		if(null==this.countParameter){
			getQueryParameter();
			this.countParameter=new QueryParameter(this.parameter.getSql(), this.parameter.getValueList());
			this.countParameter.setSql("SELECT count(*) num FROM (" + this.countParameter.getSubSql() + ") a");
		}
		return this.countParameter;
	}
	/**
	 * 返回查询实例化
	 */
	public static <T extends Model> ModelQueryList instance(Class<T> clazz){
		return new ModelQueryList(clazz,new String[]{},null,null,new HashMap<>());
	}
	/**
	 * 返回查询实例化
	 */
	public static <T extends Model> ModelQueryList instance(Class<T> clazz,String[] fieldNames,QueryPage page,QueryCondition condition){
		return new ModelQueryList(clazz,fieldNames,page,condition, new HashMap<>());
	}
	/**
	 * 返回查询实例化
	 */
	public static <T extends Model> ModelQueryList instance(Class<T> clazz,String[] fieldNames,QueryPage page,QueryCondition condition,QueryOrder... orders){
		return new ModelQueryList(clazz,fieldNames,page,condition, new HashMap<>(),orders);
	}
	/**
	 * 返回查询实例化
	 *  expressionMap  sql表达式 map<fieldname,exp>  exp中的fieldname用${}括着    exp例如:concat(#{name},'(',#{backgroud.thumPath},')')
	 */
	public static <T extends Model> ModelQueryList instance(Class<T> clazz,String[] fieldNames,QueryPage page,QueryCondition condition,Map<String,String> expressionMap,QueryOrder... orders){
		return new ModelQueryList(clazz,fieldNames,page,condition,expressionMap,orders);
	}
	public List<Map<String,Object>> getObjectList(Map<String,String> fieldExpMap) throws MException, SQLException{
		List<Map<String,Object>> list= new ArrayList<>();
		String[] fs=fieldExpMap.keySet().toArray(new String[] {});
		for(String key : fieldExpMap.keySet()){
			this.addFieldExpression(key,fieldExpMap.get(key));
		}
		QueryParameter qp=this.getQueryParameter();
		DataSet ds=DBManager.executeQuery(qp.getSql(), qp.getValueList().toArray(new Object[]{}));
		for(DataRow row : ds.rows()){
			Map<String,Object> map= new HashMap<>();
			for(String f : fs) {
				map.put(f, row.get(f));
			}
			for(String f : this.fieldNames) {
				map.put(f, row.get(this.fieldToAliasMap.get(f)));
			}
			list.add(map);
		}
		return list;
	}
	/**
	 * 查询列表
	 */
	@SuppressWarnings("unchecked")
	public static <T extends Model> List<T> getModelList(ModelQueryList modelQueryList) throws SQLException, MException{
		List<T> list= new ArrayList<>();
		QueryParameter qp=modelQueryList.getQueryParameter();
		DataSet ds=DBManager.executeQuery(qp.getSql(), qp.getValueList().toArray(new Object[]{}));
		for(DataRow row : ds.rows()){
			T model=(T) ClassUtil.newInstance(modelQueryList.table);
			for(String fn : modelQueryList.fieldNames){
				fillAttribute(model, "t0" , fn, modelQueryList.tableAMap, row);
			}
			list.add(model);
		}
		return list;
	}
	public <T extends Model> List<T> toModelList() throws SQLException, MException {
		return getModelList(this);
	}
	/**
	 * 查询列表
	 */
	public static <T extends Model> List<T> getModelList(Class<T> clazz,String... fieldNames) throws SQLException, MException {
		ModelQueryList modelQueryList=new ModelQueryList(clazz,fieldNames,null,null, new HashMap<>());
		return getModelList(modelQueryList);
	}
	/**
	 * 查询列表
	 */
	public static <T extends Model> List<T> getModelList(Class<T> clazz,String[] fieldNames,QueryPage page,QueryCondition condition,QueryOrder... orders) throws SQLException, MException{
		ModelQueryList modelQueryList=new ModelQueryList(clazz,fieldNames,page,condition, new HashMap<>(),orders);
		return getModelList(modelQueryList);
	}
	/**
	 * 查询列表
	 *  expressionMap  sql表达式 map<fieldname,exp>  exp中的fieldname用#{}括着    exp例如:concat(#{name},'(',#{backgroud.thumPath},')')
	 */
	public static <T extends Model> List<T> getModelList(Class<T> clazz,String[] fieldNames,QueryPage page,QueryCondition condition,Map<String,String> expressionMap,QueryOrder... orders) throws SQLException, MException{
		ModelQueryList modelQueryList=new ModelQueryList(clazz,fieldNames,page,condition,expressionMap,orders);
		return getModelList(modelQueryList);
	}
	/**
	 * 查询带分页信息
	 */
	public static <T extends Model> PageInfo<T> getModelPageInfo(ModelQueryList modelQueryList) throws SQLException, MException{
		List<T> list=getModelList(modelQueryList);
		QueryParameter qp=modelQueryList.getCountParameter();
		DataRow ds=DBManager.queryFirstRow(qp.getSql(), qp.getValueList().toArray(new Object[]{}));
		Integer count=null;
		if(null!=ds){
			count=ds.get(Long.class, "num").intValue();
		}
		QueryPage page=modelQueryList.page;
		if(null==page){
			page=new QueryPage();
		}
		return new PageInfo<T>(list,count,page.getIndex(),page.getNum());
	}
//	/**
//	 * 查询带分页信息
//	 */
//	public static <T extends Model> PageInfo getModelPageInfo(Class<T> clazz,String[] fieldNames,QueryPage page,QueryCondition condition,QueryOrder... orders) throws SQLException, MException{
//		ModelQueryList modelQueryList=new ModelQueryList(clazz,fieldNames,page,condition,false,new HashMap<String,String>(),orders);
//		return getModelPageInfo(modelQueryList);
//	}

	private static <T extends Model> void fillAttribute(T model,String a,String attr,Map<String,String> tableAMap,DataRow row) throws MException {
		if("oid".equals(attr)){
			ClassUtil.setFieldValue(model, attr, row.get(a + "_oid"));
		}
		int i=attr.indexOf(".");
		if(i==-1){
			Class<?> type=ClassUtil.getDeclaredField(model.getClass(),attr).getType();
			Object v=row.get(type,a + "_" + attr);
			try {
				if(v instanceof String){
					try {//字符型
						ClassUtil.setFieldValue(model, attr, v);
					}catch (Exception e){//字符型无法填充，则填成字符数组
						if(!StringUtil.isSpace((String)v))
							ClassUtil.setFieldValue(model, attr, ((String) v).split(","));
						else
							ClassUtil.setFieldValue(model, attr, new String[]{});
					}
				}else{
					ClassUtil.setFieldValue(model, attr, v);
				}
			}catch(Exception e) {
				SystemUtil.println(e.getMessage());
			}
		}else{
			String fn=attr.substring(0, i);
			String newField=attr.substring(i+1);
			Object lm=ClassUtil.getFieldValue(model, fn);
			if(null==lm){
				Map<String,LinkTableMeta> linkTableMap=ModelConfig.getLinkTableMetaMap(model.getClass());
				lm=ClassUtil.newInstance(linkTableMap.get(fn).table());
			}
			String aa="";
			for(String key : tableAMap.keySet()){
				if(key.equals(a + "." + fn)){
					aa=tableAMap.get(key);
					break;
				}
			}
			if(newField.equals("oid")){
				Object tmp=row.get(a + "_" + fn);
				if(null!=tmp){
					ClassUtil.setFieldValue(lm, "oid", tmp);
				}
				ClassUtil.setFieldValue(model, fn, lm);
			}else{
				fillAttribute((Model)lm, aa, newField, tableAMap, row);
				ClassUtil.setFieldValue(model, fn, lm);
			}
		}
	}
	@SuppressWarnings("unchecked")
	public static <T extends Model> T getModel(T obj,int level) throws Exception{
		List<T> list=(List<T>) getModelList(obj.getClass(), getFieldList(obj.getClass(),"",level).toArray(new String[]{}), new QueryPage(0,1), QueryCondition.eq("oid", obj.getOid()));
		if(!list.isEmpty()){
			return list.get(0);
		}else{
			return null;
		}
	}
	public static <T extends Model> T getModel(T obj) throws Exception{
		return getModel(obj,0);
	}
	public <T extends Model> T getModel() throws SQLException, MException {
		List<T> list=getModelList(this);
		if(!list.isEmpty()){
			return list.get(0);
		}else{
			return null;
		}
	}
	/**
	 * 获取对象
	 */
	public static <T extends Model> T getModel(T obj,String... fieldNames) throws Exception{
		return (T) getModel(obj.getClass(),obj.getOid(), fieldNames);
	}
	public static <T extends Model> T getModel(Class<T> clazz,String oid,String... fieldNames) throws Exception{
		ModelQueryList util=ModelQueryList.instance(clazz, fieldNames, new QueryPage(0,1), QueryCondition.eq("oid", oid));
		List<T> list= getModelList(util);
		if(!list.isEmpty()){
			return list.get(0);
		}else{
			return null;
		}
	}
	public static<T extends Model> T getModel(Class<T> clazz,String[] fieldNames,QueryCondition condition) throws SQLException, MException {
		return getModel(clazz,fieldNames,condition,QueryOrder.desc("oid"));
	}
	public static<T extends Model> T getModel(Class<T> clazz,String[] fieldNames,QueryCondition condition,QueryOrder... order) throws SQLException, MException {
		List<T> list= getModelList(clazz, fieldNames, new QueryPage(0,1), condition,order);
		if(!list.isEmpty()){
			return list.get(0);
		}else{
			return null;
		}
	}

	private static <T extends Model> List<String> getFieldList(Class<T> clazz,String prefix,int level) {
		List<String> fieldList= new ArrayList<>();
		Map<String,FieldMeta> fieldMap=ModelConfig.getFieldMetaMap(clazz);
		Map<String,LinkTableMeta> linkTableMap=ModelConfig.getLinkTableMetaMap(clazz);
		fieldList.add(prefix + (StringUtil.isSpace(prefix) ? "" : ".") + "oid");
		for(String field : fieldMap.keySet()){
			fieldList.add(prefix + (StringUtil.isSpace(prefix) ? "" : ".") + field);
		}
		if(level>0){
			for(String field : linkTableMap.keySet()){
				fieldList.addAll(getFieldList(linkTableMap.get(field).table(), prefix + (StringUtil.isSpace(prefix) ? "" : ".") + field,level-1));
			}
		}else{
			for(String field : linkTableMap.keySet()){
				fieldList.add(prefix + (StringUtil.isSpace(prefix) ? "" : ".") + field + ".oid");
			}
		}
		return fieldList;
	}

}